package com.jwj.testback.recordsheet.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jwj.testback.recordsheet.pojo.FieldGet;
import com.jwj.testback.recordsheet.pojo.Recordsheet;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.sql.ResultSet;
import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author JWJ
 * @since 2021-12-17
 */
public interface RecordsheetMapper extends BaseMapper<Recordsheet> {



    //查找时间字段是否存在
    @Select("select count(*) from information_schema.columns c where c.TABLE_SCHEMA=#{databaseName} and \n" +
            " c.table_name =#{tableName}  and column_name = #{fieldTime} and (c.DATA_TYPE='datetime' OR c.DATA_TYPE='date' OR c.DATA_TYPE='timestamp' OR c.DATA_TYPE='time' or\n" +
            " c.DATA_TYPE='year')")
    Integer findTimeField(String databaseName,String tableName,String fieldTime);

    @Select("select count(*) from information_schema.columns c where c.TABLE_SCHEMA=#{databaseName} and\n" +
            " c.table_name = #{tableName} and column_name = #{customField} and c.DATA_TYPE='tinyint'")
    Integer findCustomField(String databaseName,String tableName,String customField);

    //根据id查询某时间字段的值
    @Select("SELECT ${fieldTime} FROM ${databaseName}.${tableName} WHERE ${tableIdName}= ${Id}")
    Date findDate(String fieldTime,String databaseName,String tableName,String tableIdName,Long Id);

    @Select("SELECT ${customField} FROM ${databaseName}.${tableName} WHERE ${tableIdName}= ${Id}")
    int findTinyInt(String customField,String databaseName,String tableName,String tableIdName,Long Id);

    @Select("SELECT ${fieldTime} AS fieldTime,${customField} AS customField FROM ${databaseName}.${tableName} WHERE ${tableIdName}= ${Id}")
    FieldGet fieldGet(String fieldTime, String customField, String databaseName, String tableName, String tableIdName, Long Id);

    //分页查询数据
    @Select("SELECT ${tableIdName} FROM ${databaseName}.${tableName} ORDER BY ${tableIdName} ASC LIMIT ${offset},${pageSize}")
    List<Long> PagData(String databaseName,String tableName,String tableIdName,Integer offset,Integer pageSize);

//    //根据id大小分页查询数据
//    @Select("SELECT * FROM ${databaseName}.${tableName} WHERE ${tableIdName}>${lastId} ORDER BY ${tableIdName} ASC LIMIT ${offset},${pageSize}")
//    List<Long> LastIdPagData(String databaseName,String tableName,Long lastId,String tableIdName,Integer offset,Integer pageSize);

    //判断数据库是否存在
    @Select("SELECT count(*) FROM information_schema.SCHEMATA where SCHEMA_NAME= #{newdataName}")
    int whetherBase(String newdataName);

    //判断数据库中的数据表是否存在
    @Select("SELECT count(*) FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA=#{newdataName} and t.TABLE_NAME=#{newtableName}")
    int whetherTable(String newdataName,String newtableName);

    //创建数据库
    @Select("CREATE DATABASE ${newdataName}")
    void createLibrary(String newdataName);

    //复制表结构及索引
    @Select("CREATE TABLE ${newdataName}.${newtableName} LIKE ${databaseName}.${tableName}")
    String addTable(String newdataName,String newtableName,String databaseName,String tableName);

    //查询表中的主键字段
    @Select("SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name=#{tableName} AND CONSTRAINT_SCHEMA=#{databaseName} AND constraint_name='PRIMARY'")
    String findIdName(String databaseName,String tableName);

    //插入数据
    @Insert("INSERT INTO ${newdataName}.${newtableName} SELECT * FROM ${databaseName}.${tableName} WHERE ${tableIdName}= ${Id}")
    int insertData(String newdataName,String newtableName,String databaseName,String tableName,String tableIdName,Long Id);

    //查询总数据数量
    @Select("SELECT COUNT(*) FROM ${databaseName}.${tableName}")
    int count(String databaseName,String tableName);

//    //删除全部数据
//    @Delete("DELETE FROM ${databaseName}.${tableName} ORDER BY ${tableIdName} ASC LIMIT 1000")
//    Boolean delSure(String databaseName,String tableName,String tableIdName);

    //自定义时间插入
    //根据时间复制数据到新表中
    @Select("SELECT ${tableIdName} FROM ${databaseName}.${tableName} WHERE ${timeField} >= #{customStart} and ${timeField} <= #{customEnd} ORDER BY ${tableIdName} ASC LIMIT ${offset},${pageSize}")
    List<Long> customData(String databaseName,String tableName,String tableIdName,String timeField, String customStart, String customEnd,Integer offset,Integer pageSize);

    //按时间查询数据总数
    @Select("SELECT ${tableIdName} FROM ${databaseName}.${tableName} WHERE ${timeField} >= #{customStart} and ${timeField} <= #{customEnd}")
    List<Long> allCustomId(String tableIdName,String databaseName,String tableName,String timeField, String customStart, String customEnd);

    //查询记录表所有数据
    @Select("SELECT * FROM recordsheet ORDER BY id DESC")
    List<Recordsheet> showRecord();

    //删除一个记录
//    @Delete("DELETE FROM recordsheet WHERE id = ${id}")
//    int delRecord(Long id);

    //添加
    @Insert("<script>"
            +"INSERT INTO ${newdataName}.${newtableName} "
            +"select * from ${databaseName}.${tableName} where ${tableIdName} in"
            + "<foreach item='id' index='index' collection='list'      open='(' separator=',' close=')'>"
            + "#{id}"
            + "</foreach>"
            + "</script>")
    int testInsert(String newdataName,String newtableName,String databaseName,String tableName,String tableIdName,@Param("list") List<Long> list);

    //删除
//    @Delete("<script>"
//            +"delete from ${databaseName}.${tableName} where ${tableIdName} in"
//            + "<foreach item='id' index='index' collection='list'      open='(' separator=',' close=')'>"
//            + "#{id}"
//            + "</foreach>"
//            + "</script>")
//    int testDel(String databaseName,String tableName,String tableIdName,@Param("list") List<Long> list);

    @Select("SHOW TABLES FROM ${newdataName}")
    List<String> ShowAllTB(String newdataName);

    //修改表名
    @Select("rename table ${newdataName}.${newtableName} to ${newdataName}.${tableName};")
    void rename(String tableName,String newtableName,String newdataName);

//    //模糊查询表
//    @Select("SELECT table_name FROM information_schema.tables WHERE table_schema=#{newdataName} AND table_name LIKE concat(#{tableName},'%') ")
//    List<String> likeTB(String newdataName,String tableName);

    @Delete("drop table ${newdataName}.${tableName}")
    void delTB(String newdataName,String tableName);

    @Delete("DROP DATABASE IF EXISTS ${newdataName}")
    void delDataBase(String newdataName);

//    //任意条的查询
//    @Select("SELECT * FROM ${databaseName}.${tableName} ORDER BY ${mainId} ASC LIMIT ${pageSize}")
//    FinanceRecordTb listData(String databaseName,String tableName,String mainId,Integer pageSize);
}
